﻿Imports System
Imports System.Data
Imports System.Configuration
Imports System.Linq
Imports System.Web
Imports System.Web.Security
Imports System.Web.UI
Imports System.Web.UI.HtmlControls
Imports System.Web.UI.WebControls
Imports System.Web.UI.WebControls.WebParts
Imports System.Xml.Linq

Imports System.Collections.Generic
Imports Elementos
Imports System.Data.SqlClient


Namespace BBDD
    _

    Public Class Consultas
        Private Shared cnn As SqlConnection
        Public Shared CADENA_CONEXION_UAXINVER As String = "Server=UAXINVER;database=UAX_INVERSIONES;Integrated security = yes"
        'Usuarios
        Public Shared Function getUsuario(ByVal DNI As String) As Usuario
            Dim query As String = ("SELECT * FROM Usuarios WHERE DNI = @DNI")
            cnn = New SqlConnection(CADENA_CONEXION_UAXINVER)
            cnn.Open()
            Dim com As New SqlCommand(query, cnn)
            com.Parameters.Add("@DNI", SqlDbType.NVarChar, 50)
            com.Parameters(0).Value = DNI
            Dim sqldr As SqlDataReader = com.ExecuteReader()
            Dim lista As List(Of String()) = New List(Of String())
            Dim datos(sqldr.VisibleFieldCount) As String
            While sqldr.Read()
                Dim i As Integer
                For i = 0 To sqldr.VisibleFieldCount - 1
                    datos(i) = sqldr(i)
                Next i
                lista.Add(datos)
            End While
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If


            Dim usuario As Usuario = Nothing
            If lista.Count > 0 Then
                Dim attr As String() = lista.ElementAt(0)
                usuario = New Usuario(attr)
            End If
            Return usuario
        End Function 'getUsuario

        Public Shared Function insertarUsuario(ByVal DNI As String, ByVal nombre As String, ByVal apellidos As String, ByVal email As String, ByVal direccion As String, ByVal telefono As Integer, ByVal localidad As String, ByVal provincia As String, ByVal pais As String, ByVal CP As Integer, ByVal saldo As Double, ByVal CC_banco As String, ByVal contrasena As String, ByVal privilegios As Integer, ByVal esPendiente As Integer) As Boolean
            Dim insert As String = "INSERT INTO Usuarios (DNI, nombre, apellidos, email, direccion, telefono, localidad, provincia, pais, CP, saldo, CC_banco, contrasena, privilegios, esPendiente) VALUES (@DNI , @nombre , @apellidos, @email, @direccion , @telefono,@localidad, @provincia, @pais, @CP, @saldo , @CC_banco , @contrasena  , @privilegios , @esPendiente)"

            cnn = New SqlConnection(CADENA_CONEXION_UAXINVER)
            cnn.Open()
            Dim com As SqlCommand = New SqlCommand(insert, cnn)

            com.Parameters.Add("@apellidos", SqlDbType.NVarChar, 50)
            com.Parameters(0).Value = apellidos

            com.Parameters.Add("@telefono", SqlDbType.Int)
            com.Parameters(1).Value = telefono

            com.Parameters.Add("@nombre", SqlDbType.NVarChar, 50)
            com.Parameters(2).Value = nombre

            com.Parameters.Add("@contrasena", SqlDbType.NVarChar, 50)
            com.Parameters(3).Value = contrasena

            com.Parameters.Add("@DNI", SqlDbType.NVarChar, 50)
            com.Parameters(4).Value = DNI

            com.Parameters.Add("@email", SqlDbType.NVarChar, 50)
            com.Parameters(5).Value = email

            com.Parameters.Add("@direccion", SqlDbType.NVarChar, 50)
            com.Parameters(6).Value = direccion

            com.Parameters.Add("@localidad", SqlDbType.NVarChar, 50)
            com.Parameters(7).Value = localidad

            com.Parameters.Add("@provincia", SqlDbType.NVarChar, 50)
            com.Parameters(8).Value = provincia

            com.Parameters.Add("@pais", SqlDbType.NVarChar, 50)
            com.Parameters(9).Value = pais

            com.Parameters.Add("@CP", SqlDbType.Int)
            com.Parameters(10).Value = CP

            com.Parameters.Add("@saldo", SqlDbType.Int)
            com.Parameters(11).Value = saldo

            com.Parameters.Add("@CC_banco", SqlDbType.NVarChar, 50)
            com.Parameters(12).Value = CC_banco

            com.Parameters.Add("@privilegios", SqlDbType.NVarChar, 50)
            com.Parameters(13).Value = privilegios

            com.Parameters.Add("@esPendiente", SqlDbType.Int)
            com.Parameters(14).Value = esPendiente



            Dim res As Integer

            res = com.ExecuteNonQuery()

            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If

            'Si ha habido coincidencias
            If res > 0 Then
                Return True
            End If

            Return False
        End Function 'insertarUsuario

        Public Shared Function borrarUsuario(ByVal DNI As String) As Boolean
            Dim query As String = "DELETE FROM Usuarios WHERE DNI = @DNI"
            cnn = New SqlConnection(CADENA_CONEXION_UAXINVER)
            cnn.Open()
            Dim com As SqlCommand = New SqlCommand(query, cnn)
            com.Parameters.Add("@DNI", SqlDbType.NVarChar, 50)
            com.Parameters(0).Value = DNI

            Dim res As Integer

            res = com.ExecuteNonQuery()

            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            If res > 0 Then
                Return True
            End If
            Return False
        End Function 'borrarUsuario


        Public Shared Function modificarUsuario(ByVal DNI As String, ByVal nombre As String, ByVal apellidos As String, ByVal email As String, ByVal direccion As String, ByVal telefono As Integer, ByVal localidad As String, ByVal provincia As String, ByVal pais As String, ByVal CP As Integer, ByVal saldo As Double, ByVal CC_banco As String, ByVal pass As String, ByVal privilegios As Integer, ByVal esPendiente As Integer) As Boolean

            Dim query As String = "UPDATE Usuarios SET apellidos = @apellidos , telefono = @telefono , nombre = @nombre , contrasena = @pass , email = @email , direccion = @direccion , localidad = @localidad  , provincia = @provincia  , CP = @CP  ,  CC_banco = @CC_banco , esPendiente = @esPendiente  , pais = @pais WHERE DNI = @DNI"
            cnn = New SqlConnection(CADENA_CONEXION_UAXINVER)
            cnn.Open()
            Dim com As SqlCommand = New SqlCommand(query, cnn)

            com.Parameters.Add("@apellidos", SqlDbType.NVarChar, 50)
            com.Parameters(0).Value = apellidos

            com.Parameters.Add("@telefono", SqlDbType.Int)
            com.Parameters(1).Value = telefono

            com.Parameters.Add("@nombre", SqlDbType.NVarChar, 50)
            com.Parameters(2).Value = nombre

            com.Parameters.Add("@pass", SqlDbType.NVarChar, 50)
            com.Parameters(3).Value = pass

            com.Parameters.Add("@DNI", SqlDbType.NVarChar, 50)
            com.Parameters(4).Value = DNI

            com.Parameters.Add("@email", SqlDbType.NVarChar, 50)
            com.Parameters(5).Value = email

            com.Parameters.Add("@direccion", SqlDbType.NVarChar, 50)
            com.Parameters(6).Value = direccion

            com.Parameters.Add("@localidad", SqlDbType.NVarChar, 50)
            com.Parameters(7).Value = localidad

            com.Parameters.Add("@provincia", SqlDbType.NVarChar, 50)
            com.Parameters(8).Value = provincia

            com.Parameters.Add("@pais", SqlDbType.NVarChar, 50)
            com.Parameters(9).Value = pais

            com.Parameters.Add("@CP", SqlDbType.Int)
            com.Parameters(10).Value = CP

            com.Parameters.Add("@saldo", SqlDbType.Int)
            com.Parameters(11).Value = saldo

            com.Parameters.Add("@CC_banco", SqlDbType.NVarChar, 50)
            com.Parameters(12).Value = CC_banco

            com.Parameters.Add("@privilegios", SqlDbType.NVarChar, 50)
            com.Parameters(13).Value = privilegios

            com.Parameters.Add("@esPendiente", SqlDbType.Int)
            com.Parameters(14).Value = esPendiente

            Dim res As Integer

            res = com.ExecuteNonQuery()

            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            If res > 0 Then
                Return True
            End If
            Return False
        End Function 'modificarUsuario



        Public Shared Function getnombre(ByVal DNI As String) As String
            Dim query As String = "SELECT nombre FROM Usuarios WHERE DNI = @DNI"
            cnn = New SqlConnection(CADENA_CONEXION_UAXINVER)
            cnn.Open()
            Dim com As New SqlCommand(query, cnn)
            com.Parameters.Add("@DNI", SqlDbType.NVarChar, 50)
            com.Parameters(0).Value = DNI
            Dim sqldr As SqlDataReader = com.ExecuteReader()
            Dim lista As List(Of String()) = New List(Of String())
            Dim datos(sqldr.VisibleFieldCount) As String
            While sqldr.Read()
                Dim i As Integer
                For i = 0 To sqldr.VisibleFieldCount - 1
                    datos(i) = sqldr(i)
                Next i
                lista.Add(datos)
            End While
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            Return lista.First.GetValue(0)
        End Function 'getnombre

        Public Shared Function getapellidos(ByVal DNI As String) As String
            Dim query As String = "SELECT apellidos FROM Usuarios WHERE DNI = @DNI"
            cnn = New SqlConnection(CADENA_CONEXION_UAXINVER)
            cnn.Open()
            Dim com As New SqlCommand(query, cnn)
            com.Parameters.Add("@DNI", SqlDbType.NVarChar, 50)
            com.Parameters(0).Value = DNI
            Dim sqldr As SqlDataReader = com.ExecuteReader()
            Dim lista As List(Of String()) = New List(Of String())
            Dim datos(sqldr.VisibleFieldCount) As String
            While sqldr.Read()
                Dim i As Integer
                For i = 0 To sqldr.VisibleFieldCount - 1
                    datos(i) = sqldr(i)
                Next i
                lista.Add(datos)
            End While
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            Return lista.First.GetValue(0)
        End Function 'getapellidos

        Public Shared Function getemail(ByVal DNI As String) As String
            Dim query As String = "SELECT email FROM Usuarios WHERE DNI = @DNI"
            cnn = New SqlConnection(CADENA_CONEXION_UAXINVER)
            cnn.Open()
            Dim com As New SqlCommand(query, cnn)
            com.Parameters.Add("@DNI", SqlDbType.NVarChar, 50)
            com.Parameters(0).Value = DNI
            Dim sqldr As SqlDataReader = com.ExecuteReader()
            Dim lista As List(Of String()) = New List(Of String())
            Dim datos(sqldr.VisibleFieldCount) As String
            While sqldr.Read()
                Dim i As Integer
                For i = 0 To sqldr.VisibleFieldCount - 1
                    datos(i) = sqldr(i)
                Next i
                lista.Add(datos)
            End While
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            Return lista.First.GetValue(0)

        End Function 'getemail


        Public Shared Sub setemail(ByVal DNI As String, ByVal emailUser As String)
            Dim query As String = ""
            query = "UPDATE Usuarios SET email = @email WHERE DNI = @DNI"
            cnn = New SqlConnection(CADENA_CONEXION_UAXINVER)
            cnn.Open()
            Dim com As SqlCommand = New SqlCommand(query, cnn)

            com.Parameters.Add("@email", SqlDbType.NVarChar, 50)
            com.Parameters(0).Value = emailUser

            com.Parameters.Add("@DNI", SqlDbType.NVarChar, 50)
            com.Parameters(1).Value = DNI

            Dim res As Integer

            res = com.ExecuteNonQuery()

            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
        End Sub 'setemail

        Public Shared Function getdireccion(ByVal DNI As String) As String
            Dim query As String = "SELECT direccion FROM Usuarios WHERE DNI = @DNI"
            cnn = New SqlConnection(CADENA_CONEXION_UAXINVER)
            cnn.Open()
            Dim com As New SqlCommand(query, cnn)
            com.Parameters.Add("@DNI", SqlDbType.NVarChar, 50)
            com.Parameters(0).Value = DNI
            Dim sqldr As SqlDataReader = com.ExecuteReader()
            Dim lista As List(Of String()) = New List(Of String())
            Dim datos(sqldr.VisibleFieldCount) As String
            While sqldr.Read()
                Dim i As Integer
                For i = 0 To sqldr.VisibleFieldCount - 1
                    datos(i) = sqldr(i)
                Next i
                lista.Add(datos)
            End While
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            Return lista.First.GetValue(0)
        End Function 'getdireccion

        Public Shared Sub setdireccion(ByVal DNI As String, ByVal direccionUser As String)
            Dim query As String = ""
            query = "UPDATE Usuarios SET direccion = @direccion WHERE DNI = @DNI"
            cnn = New SqlConnection(CADENA_CONEXION_UAXINVER)
            cnn.Open()
            Dim com As SqlCommand = New SqlCommand(query, cnn)

            com.Parameters.Add("@direccion", SqlDbType.NVarChar, 50)
            com.Parameters(0).Value = direccionUser

            com.Parameters.Add("@DNI", SqlDbType.NVarChar, 50)
            com.Parameters(1).Value = DNI

            Dim res As Integer

            res = com.ExecuteNonQuery()

            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
        End Sub 'setdireccion

        Public Shared Function gettelefono(ByVal DNI As String) As Long
            Dim query As String = "SELECT telefono FROM Usuarios WHERE DNI = @DNI"
            cnn = New SqlConnection(CADENA_CONEXION_UAXINVER)
            cnn.Open()
            Dim com As New SqlCommand(query, cnn)
            com.Parameters.Add("@DNI", SqlDbType.NVarChar, 50)
            com.Parameters(0).Value = DNI
            Dim sqldr As SqlDataReader = com.ExecuteReader()
            Dim lista As List(Of String()) = New List(Of String())
            Dim datos(sqldr.VisibleFieldCount) As String
            While sqldr.Read()
                Dim i As Integer
                For i = 0 To sqldr.VisibleFieldCount - 1
                    datos(i) = sqldr(i)
                Next i
                lista.Add(datos)
            End While
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            Return lista.First.GetValue(0)
        End Function 'gettelefono

        Public Shared Sub settelefono(ByVal DNI As String, ByVal telefonoUser As Long)
            Dim query As String = ""
            query = "UPDATE Usuarios SET telefono = @telefono WHERE DNI = @DNI"
            cnn = New SqlConnection(CADENA_CONEXION_UAXINVER)
            cnn.Open()
            Dim com As SqlCommand = New SqlCommand(query, cnn)

            com.Parameters.Add("@telefono", SqlDbType.NVarChar, 50)
            com.Parameters(0).Value = telefonoUser

            com.Parameters.Add("@DNI", SqlDbType.NVarChar, 50)
            com.Parameters(1).Value = DNI

            Dim res As Integer

            res = com.ExecuteNonQuery()

            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
        End Sub 'settelefono

        Public Shared Function getlocalidad(ByVal DNI As String) As String
            Dim query As String = "SELECT localidad FROM Usuarios WHERE DNI = @DNI"
            cnn = New SqlConnection(CADENA_CONEXION_UAXINVER)
            cnn.Open()
            Dim com As New SqlCommand(query, cnn)
            com.Parameters.Add("@DNI", SqlDbType.NVarChar, 50)
            com.Parameters(0).Value = DNI
            Dim sqldr As SqlDataReader = com.ExecuteReader()
            Dim lista As List(Of String()) = New List(Of String())
            Dim datos(sqldr.VisibleFieldCount) As String
            While sqldr.Read()
                Dim i As Integer
                For i = 0 To sqldr.VisibleFieldCount - 1
                    datos(i) = sqldr(i)
                Next i
                lista.Add(datos)
            End While
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            Return lista.First.GetValue(0)
           
        End Function 'getlocalidad

        Public Shared Sub setlocalidad(ByVal DNI As String, ByVal localidadUser As String)
            Dim query As String = ""
            query = "UPDATE Usuarios SET localidad = @localidad WHERE DNI = @DNI"
            cnn = New SqlConnection(CADENA_CONEXION_UAXINVER)
            cnn.Open()
            Dim com As SqlCommand = New SqlCommand(query, cnn)
            
            com.Parameters.Add("@localidad", SqlDbType.NVarChar, 50)
            com.Parameters(0).Value = localidadUser

            com.Parameters.Add("@DNI", SqlDbType.NVarChar, 50)
            com.Parameters(1).Value = DNI

            Dim res As Integer

            res = com.ExecuteNonQuery()

            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
        End Sub 'setlocalidad

        Public Shared Function getprovincia(ByVal DNI As String) As String
            Dim query As String = "SELECT provincia FROM Usuarios WHERE DNI = @DNI"
            cnn = New SqlConnection(CADENA_CONEXION_UAXINVER)
            cnn.Open()
            Dim com As New SqlCommand(query, cnn)
            com.Parameters.Add("@DNI", SqlDbType.NVarChar, 50)
            com.Parameters(0).Value = DNI
            Dim sqldr As SqlDataReader = com.ExecuteReader()
            Dim lista As List(Of String()) = New List(Of String())
            Dim datos(sqldr.VisibleFieldCount) As String
            While sqldr.Read()
                Dim i As Integer
                For i = 0 To sqldr.VisibleFieldCount - 1
                    datos(i) = sqldr(i)
                Next i
                lista.Add(datos)
            End While
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            Return lista.First.GetValue(0)
        End Function 'getprovincia

        Public Shared Sub setprovincia(ByVal DNI As String, ByVal provinciaUser As String)
            Dim query As String = ""
            query = "UPDATE Usuarios SET provincia = @provincia WHERE DNI = @DNI"
            cnn = New SqlConnection(CADENA_CONEXION_UAXINVER)
            cnn.Open()
            Dim com As SqlCommand = New SqlCommand(query, cnn)

            com.Parameters.Add("@DNI", SqlDbType.NVarChar, 50)
            com.Parameters(0).Value = DNI

            com.Parameters.Add("@provincia", SqlDbType.NVarChar, 50)
            com.Parameters(1).Value = provinciaUser

            Dim res As Integer

            res = com.ExecuteNonQuery()

            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
        End Sub 'setprovincia

        Public Shared Function getpais(ByVal DNI As String) As String
            Dim query As String = "SELECT pais FROM Usuarios WHERE DNI = @DNI"
            cnn = New SqlConnection(CADENA_CONEXION_UAXINVER)
            cnn.Open()
            Dim com As SqlCommand = New SqlCommand(query, cnn)

            com.Parameters.Add("@DNI", SqlDbType.NVarChar, 50)
            com.Parameters(0).Value = DNI

            Dim sqldr As SqlDataReader = com.ExecuteReader()
            Dim lista As List(Of String()) = New List(Of String())
            Dim datos(sqldr.VisibleFieldCount) As String
            While sqldr.Read()
                Dim i As Integer
                For i = 0 To sqldr.VisibleFieldCount - 1
                    datos(i) = sqldr(i)
                Next i
                lista.Add(datos)
            End While
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            Return lista.First.GetValue(0)
            
        End Function 'getpais

        Public Shared Sub setpais(ByVal DNI As String, ByVal paisUser As String)
            Dim query As String = ""
            query = "UPDATE Usuarios SET pais = @pais WHERE DNI = @DNI"
            cnn = New SqlConnection(CADENA_CONEXION_UAXINVER)
            cnn.Open()
            Dim com As SqlCommand = New SqlCommand(query, cnn)

            com.Parameters.Add("@DNI", SqlDbType.NVarChar, 50)
            com.Parameters(0).Value = DNI

            com.Parameters.Add("@pais", SqlDbType.NVarChar, 50)
            com.Parameters(1).Value = paisUser

            Dim res As Integer

            res = com.ExecuteNonQuery()

            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
        End Sub 'setpais

        Public Shared Function getCP(ByVal DNI As String) As Integer
            Dim query As String = "SELECT CP FROM Usuarios WHERE DNI = @DNI"
            cnn = New SqlConnection(CADENA_CONEXION_UAXINVER)
            cnn.Open()
            Dim com As SqlCommand = New SqlCommand(query, cnn)

            com.Parameters.Add("@DNI", SqlDbType.NVarChar, 50)
            com.Parameters(0).Value = DNI

            Dim sqldr As SqlDataReader = com.ExecuteReader()
            Dim lista As List(Of String()) = New List(Of String())
            Dim datos(sqldr.VisibleFieldCount) As String
            While sqldr.Read()
                Dim i As Integer
                For i = 0 To sqldr.VisibleFieldCount - 1
                    datos(i) = sqldr(i)
                Next i
                lista.Add(datos)
            End While
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            Return lista.First.GetValue(0)
        End Function 'getCP

        Public Shared Sub setCP(ByVal DNI As String, ByVal CPUser As Integer)
            Dim query As String = ""
            query = "UPDATE Usuarios SET CP = @CP WHERE DNI = @DNI"
            cnn = New SqlConnection(CADENA_CONEXION_UAXINVER)
            cnn.Open()
            Dim com As SqlCommand = New SqlCommand(query, cnn)

            com.Parameters.Add("@DNI", SqlDbType.NVarChar, 50)
            com.Parameters(0).Value = DNI

            com.Parameters.Add("@CP", SqlDbType.NVarChar, 50)
            com.Parameters(1).Value = CPUser

            Dim res As Integer

            res = com.ExecuteNonQuery()

            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
        End Sub 'setCP

        Public Shared Function getsaldo(ByVal DNI As String) As Double

            Dim query As String = "SELECT saldo FROM Usuarios WHERE DNI = @DNI"
            cnn = New SqlConnection(CADENA_CONEXION_UAXINVER)
            cnn.Open()
            Dim com As SqlCommand = New SqlCommand(query, cnn)

            com.Parameters.Add("@DNI", SqlDbType.NVarChar, 50)
            com.Parameters(0).Value = DNI

            Dim sqldr As SqlDataReader = com.ExecuteReader()
            Dim lista As List(Of String()) = New List(Of String())
            Dim datos(sqldr.VisibleFieldCount) As String
            While sqldr.Read()
                Dim i As Integer
                For i = 0 To sqldr.VisibleFieldCount - 1
                    datos(i) = sqldr(i)
                Next i
                lista.Add(datos)
            End While
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            Return lista.First.GetValue(0)

        End Function 'getsaldo

        Public Shared Sub setsaldo(ByVal DNI As String, ByVal saldoEvaluable As Double)
            Dim query As String = ""
            query = "UPDATE Usuarios SET saldo = @saldo WHERE DNI = @DNI"
            cnn = New SqlConnection(CADENA_CONEXION_UAXINVER)
            cnn.Open()
            Dim com As SqlCommand = New SqlCommand(query, cnn)

            com.Parameters.Add("@DNI", SqlDbType.NVarChar, 50)
            com.Parameters(0).Value = DNI

            com.Parameters.Add("@saldo", SqlDbType.NVarChar, 50)
            com.Parameters(1).Value = saldoEvaluable

            Dim res As Integer

            res = com.ExecuteNonQuery()

            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
        End Sub 'setsaldo

        Public Shared Function getCCBanco(ByVal DNI As String) As String
            Dim query As String = "SELECT CC_banco FROM Usuarios WHERE DNI = @DNI"
            cnn = New SqlConnection(CADENA_CONEXION_UAXINVER)
            cnn.Open()
            Dim com As SqlCommand = New SqlCommand(query, cnn)

            com.Parameters.Add("@DNI", SqlDbType.NVarChar, 50)
            com.Parameters(0).Value = DNI

            Dim sqldr As SqlDataReader = com.ExecuteReader()
            Dim lista As List(Of String()) = New List(Of String())
            Dim datos(sqldr.VisibleFieldCount) As String
            While sqldr.Read()
                Dim i As Integer
                For i = 0 To sqldr.VisibleFieldCount - 1
                    datos(i) = sqldr(i)
                Next i
                lista.Add(datos)
            End While
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            Return lista.First.GetValue(0)
           
        End Function 'getCCBanco

        Public Shared Sub setCCBanco(ByVal DNI As String, ByVal ccBancoUser As String)
            Dim query As String = ""
            query = "UPDATE Usuarios SET CC_banco = @CC_banco WHERE DNI = @DNI"
            cnn = New SqlConnection(CADENA_CONEXION_UAXINVER)
            cnn.Open()
            Dim com As SqlCommand = New SqlCommand(query, cnn)

            com.Parameters.Add("@DNI", SqlDbType.NVarChar, 50)
            com.Parameters(0).Value = DNI

            com.Parameters.Add("@CC_banco", SqlDbType.NVarChar, 50)
            com.Parameters(1).Value = ccBancoUser

            Dim res As Integer

            res = com.ExecuteNonQuery()

            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
        End Sub 'setCCBanco

        Public Shared Function getcontrasena(ByVal DNI As String) As String
            Dim query As String = "SELECT contrasena FROM Usuarios WHERE DNI = @DNI"
            cnn = New SqlConnection(CADENA_CONEXION_UAXINVER)
            cnn.Open()
            Dim com As SqlCommand = New SqlCommand(query, cnn)

            com.Parameters.Add("@DNI", SqlDbType.NVarChar, 50)
            com.Parameters(0).Value = DNI

            Dim sqldr As SqlDataReader = com.ExecuteReader()
            Dim lista As List(Of String()) = New List(Of String())
            Dim datos(sqldr.VisibleFieldCount) As String
            While sqldr.Read()
                Dim i As Integer
                For i = 0 To sqldr.VisibleFieldCount - 1
                    datos(i) = sqldr(i)
                Next i
                lista.Add(datos)
            End While
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            Return lista.First.GetValue(0)
           
        End Function 'getcontrasena

        Public Shared Sub setcontrasena(ByVal DNI As String, ByVal contrasenaUser As String)
            Dim query As String = ""
            query = "UPDATE Usuarios SET contrasena = @contrasena WHERE DNI = @DNI"
            cnn = New SqlConnection(CADENA_CONEXION_UAXINVER)
            cnn.Open()
            Dim com As SqlCommand = New SqlCommand(query, cnn)

            com.Parameters.Add("@DNI", SqlDbType.NVarChar, 50)
            com.Parameters(0).Value = DNI

            com.Parameters.Add("@contrasena", SqlDbType.NVarChar, 50)
            com.Parameters(1).Value = contrasenaUser

            Dim res As Integer

            res = com.ExecuteNonQuery()

            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
        End Sub 'setcontrasena

        Public Shared Function getprivilegios(ByVal DNI As String) As String
            Dim query As String = "SELECT privilegios FROM Usuarios WHERE DNI = @DNI"
            cnn = New SqlConnection(CADENA_CONEXION_UAXINVER)
            cnn.Open()
            Dim com As SqlCommand = New SqlCommand(query, cnn)

            com.Parameters.Add("@DNI", SqlDbType.NVarChar, 50)
            com.Parameters(0).Value = DNI

            Dim sqldr As SqlDataReader = com.ExecuteReader()
            Dim lista As List(Of String()) = New List(Of String())
            Dim datos(sqldr.VisibleFieldCount) As String
            While sqldr.Read()
                Dim i As Integer
                For i = 0 To sqldr.VisibleFieldCount - 1
                    datos(i) = sqldr(i)
                Next i
                lista.Add(datos)
            End While
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            Return lista.First.GetValue(0)
            
        End Function 'getprivilegios

        Public Shared Function getesPendiente(ByVal DNI As String) As Integer
            Dim query As String = "SELECT esPendiente FROM Usuarios WHERE DNI = @DNI"
            cnn = New SqlConnection(CADENA_CONEXION_UAXINVER)
            cnn.Open()
            Dim com As SqlCommand = New SqlCommand(query, cnn)

            com.Parameters.Add("@DNI", SqlDbType.NVarChar, 50)
            com.Parameters(0).Value = DNI

            Dim sqldr As SqlDataReader = com.ExecuteReader()
            Dim lista As List(Of String()) = New List(Of String())
            Dim datos(sqldr.VisibleFieldCount) As String
            While sqldr.Read()
                Dim i As Integer
                For i = 0 To sqldr.VisibleFieldCount - 1
                    datos(i) = sqldr(i)
                Next i
                lista.Add(datos)
            End While
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            Return lista.First.GetValue(0)
            
        End Function 'getesPendiente

        Public Shared Sub setesPendiente(ByVal DNI As String, ByVal esPendienteUser As Integer)
            Dim query As String = ""
            query = "UPDATE Usuarios SET esPendiente = @esPendiente WHERE DNI = @DNI"
            cnn = New SqlConnection(CADENA_CONEXION_UAXINVER)
            cnn.Open()
            Dim com As SqlCommand = New SqlCommand(query, cnn)

            com.Parameters.Add("@DNI", SqlDbType.NVarChar, 50)
            com.Parameters(0).Value = DNI

            com.Parameters.Add("@esPendiente", SqlDbType.Int)
            com.Parameters(1).Value = esPendienteUser

            Dim res As Integer

            res = com.ExecuteNonQuery()

            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
        End Sub 'setesPendiente

        Public Shared Function existeUsuario(ByVal DNI As String) As Boolean
            Dim query As String = "SELECT DNI FROM Usuarios WHERE DNI = @DNI"
            cnn = New SqlConnection(CADENA_CONEXION_UAXINVER)
            cnn.Open()
            Dim com As New SqlCommand(query, cnn)

            com.Parameters.Add("@DNI", SqlDbType.NVarChar, 50)
            com.Parameters(0).Value = DNI

            Dim sqldr As SqlDataReader = com.ExecuteReader()
            Dim lista As List(Of String()) = New List(Of String())
            Dim datos(sqldr.VisibleFieldCount) As String
            While sqldr.Read()
                Dim i As Integer
                For i = 0 To sqldr.VisibleFieldCount - 1
                    datos(i) = sqldr(i)
                Next i
                lista.Add(datos)
            End While
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            Return lista.Count > 0
        End Function 'existeUsuario

        Public Shared Function existeemail(ByVal email As String) As Boolean
            Dim query As String = "SELECT email FROM Usuarios WHERE email = @email"
            cnn = New SqlConnection(CADENA_CONEXION_UAXINVER)
            cnn.Open()
            Dim com As New SqlCommand(query, cnn)

            com.Parameters.Add("@email", SqlDbType.NVarChar, 50)
            com.Parameters(0).Value = email

            Dim sqldr As SqlDataReader = com.ExecuteReader()
            Dim lista As List(Of String()) = New List(Of String())
            Dim datos(sqldr.VisibleFieldCount) As String
            While sqldr.Read()
                Dim i As Integer
                For i = 0 To sqldr.VisibleFieldCount - 1
                    datos(i) = sqldr(i)
                Next i
                lista.Add(datos)
            End While
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If

            Return lista.Count > 0
        End Function 'existeemail

        Public Shared Function cambiasaldo(ByVal DNI As String, ByVal saldoEvaluable As Double) As Boolean
            Dim query As String = ""

            query = "UPDATE Usuarios SET saldo = @saldo WHERE DNI = @DNI"
            cnn = New SqlConnection(CADENA_CONEXION_UAXINVER)
            cnn.Open()
            Dim com As SqlCommand = New SqlCommand(query, cnn)

            com.Parameters.Add("@DNI", SqlDbType.NVarChar, 50)
            com.Parameters(0).Value = DNI

            com.Parameters.Add("@saldo", SqlDbType.NVarChar, 50)
            com.Parameters(1).Value = saldoEvaluable

            Dim res As Integer

            res = com.ExecuteNonQuery()

            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            If res > 0 Then
                Return True
            End If
        End Function 'cambiasaldo


        Public Shared Function retiradaEfectivo(ByVal DNI As String, ByVal cantidad As Double) As Boolean

            Dim saldoActual As Double = getsaldo(DNI)
            If saldoActual > cantidad Then
                Dim nuevosaldo As Double = saldoActual - cantidad
                cambiasaldo(DNI, nuevosaldo)
                Return True
            End If

            Return False
        End Function 'retiradaEfectivo

        Public Shared Function ingresarEfectivo(ByVal DNI As String, ByVal cantidad As Double) As Boolean

            Dim saldoActual As Double = getsaldo(DNI)

            Dim nuevosaldo As Double = saldoActual + cantidad
            cambiasaldo(DNI, nuevosaldo)

            Return True
        End Function 'ingresarEfectivo




        Public Shared Function comprarAccion(ByVal DNI As String, ByVal id As String, ByVal cantidad As Integer) As Integer

            Dim saldo As Double = 0.0
            Dim query As String = "SELECT saldo FROM Usuarios WHERE DNI = @DNI "
            cnn = New SqlConnection(CADENA_CONEXION_UAXINVER)
            cnn.Open()
            Dim com As New SqlCommand(query, cnn)

            com.Parameters.Add("@DNI", SqlDbType.NVarChar, 50)
            com.Parameters(0).Value = DNI

            Dim sqldr As SqlDataReader = com.ExecuteReader()
            Dim lista As List(Of String()) = New List(Of String())
            Dim datos(sqldr.VisibleFieldCount) As String
            While sqldr.Read()
                Dim i As Integer
                For i = 0 To sqldr.VisibleFieldCount - 1
                    datos(i) = sqldr(i)
                Next i
                lista.Add(datos)
            End While
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If

            If lista.Count > 0 Then
                saldo = Double.Parse(lista.First.GetValue(0))

            Else

            End If
            query = "SELECT precio FROM Acciones WHERE id_accion = @id " 'Se obtiene el ID de la compra
            cnn = New SqlConnection(CADENA_CONEXION_UAXINVER)
            cnn.Open()
            Dim com2 As New SqlCommand(query, cnn)

            com2.Parameters.Add("@id", SqlDbType.NVarChar, 50)
            com2.Parameters(0).Value = id

            Dim sqldr2 As SqlDataReader = com2.ExecuteReader()
            Dim lista2 As List(Of String()) = New List(Of String())
            Dim datos2(sqldr2.VisibleFieldCount) As String
            While sqldr2.Read()
                Dim i As Integer
                For i = 0 To sqldr2.VisibleFieldCount - 1
                    datos2(i) = sqldr2(i)
                Next i
                lista2.Add(datos2)
            End While
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If


            Dim valoracion As Double = Double.Parse(lista2.First.GetValue(0))

            
            Dim importe As Double = cantidad * valoracion
         
            Dim saldoActualizado As Double = saldo - importe


            If saldo >= 0 Then
                query = "UPDATE Usuarios SET saldo =  @saldoActualizado WHERE DNI= @DNI " 'Saldo actualizado
                cnn = New SqlConnection(CADENA_CONEXION_UAXINVER)
                cnn.Open()
                Dim com3 As New SqlCommand(query, cnn)

                com3.Parameters.Add("@saldoActualizado", SqlDbType.Money)
                com3.Parameters(0).Value = saldoActualizado

                com3.Parameters.Add("@DNI", SqlDbType.NVarChar, 50)
                com3.Parameters(1).Value = DNI

                Dim sqldr3 As SqlDataReader = com3.ExecuteReader()
                Dim lista3 As List(Of String()) = New List(Of String())
                Dim datos3(sqldr3.VisibleFieldCount) As String
                While sqldr3.Read()
                    Dim i As Integer
                    For i = 0 To sqldr3.VisibleFieldCount - 1
                        datos3(i) = sqldr3(i)
                    Next i
                    lista3.Add(datos3)
                End While
                If cnn.State = ConnectionState.Open Then
                    cnn.Close()
                End If

                Dim fecha As DateTime = DateTime.Now
                Dim fechaStr As String = "" + fecha
                Dim insert As String = "INSERT INTO Compras(id_usuario, id_accion, cantidad, fecha, importe, precio, esPendiente, activo) VALUES (@DNI , @id , @cantidad , GetDate() , @importe  , @valoracion  , 0  ,1)"

                cnn = New SqlConnection(CADENA_CONEXION_UAXINVER)
                cnn.Open()
                Dim com4 As SqlCommand = New SqlCommand(insert, cnn)
                com4.Parameters.Add("@DNI", SqlDbType.NVarChar, 50)
                com4.Parameters(0).Value = DNI

                com4.Parameters.Add("@id", SqlDbType.NVarChar, 50)
                com4.Parameters(1).Value = id

                com4.Parameters.Add("@cantidad", SqlDbType.Int)
                com4.Parameters(2).Value = cantidad

                com4.Parameters.Add("@importe", SqlDbType.Money, 50)
                com4.Parameters(3).Value = importe

                com4.Parameters.Add("@valoracion", SqlDbType.NVarChar, 50)
                com4.Parameters(4).Value = valoracion
                Dim res As Integer = com4.ExecuteNonQuery()
                If cnn.State = ConnectionState.Open Then
                    cnn.Close()
                End If

                query = "SELECT COUNT(*) FROM Cartera WHERE DNI = @DNI AND id_accion = @id"

                cnn = New SqlConnection(CADENA_CONEXION_UAXINVER)
                cnn.Open()
                Dim com5 As New SqlCommand(query, cnn)
                com5.Parameters.Add("@DNI", SqlDbType.NVarChar, 50)
                com5.Parameters(0).Value = DNI

                com5.Parameters.Add("@id", SqlDbType.NVarChar, 50)
                com5.Parameters(1).Value = id
                Dim sqldr5 As SqlDataReader = com5.ExecuteReader()
                Dim lista5 As List(Of String()) = New List(Of String())
                Dim datos5(sqldr5.VisibleFieldCount) As String
                While sqldr5.Read()
                    Dim i As Integer
                    For i = 0 To sqldr5.VisibleFieldCount - 1
                        datos5(i) = sqldr5(i)
                    Next i
                    lista5.Add(datos5)
                End While
                If cnn.State = ConnectionState.Open Then
                    cnn.Close()
                End If
                Dim hayValor As Integer = Integer.Parse((lista5.First.GetValue(0))) '
               
                If hayValor > 0 Then
                    query = "UPDATE Cartera SET cantidad = cantidad + @cantidad , precioTotal = @importe WHERE DNI = @DNI AND id_accion = @id"
                    cnn = New SqlConnection(CADENA_CONEXION_UAXINVER)
                    cnn.Open()
                    Dim com6 As SqlCommand = New SqlCommand(query, cnn)

                    com6.Parameters.Add("@DNI", SqlDbType.NVarChar, 50)
                    com6.Parameters(0).Value = DNI

                    com6.Parameters.Add("@id", SqlDbType.NVarChar, 50)
                    com6.Parameters(1).Value = id

                    com6.Parameters.Add("@cantidad", SqlDbType.Int)
                    com6.Parameters(2).Value = cantidad

                    com6.Parameters.Add("@importe", SqlDbType.Money, 50)
                    com6.Parameters(3).Value = importe

                    Dim res6 As Integer = com6.ExecuteNonQuery()

                    If cnn.State = ConnectionState.Open Then
                        cnn.Close()
                    End If
                    Return res6
                Else
                    insert = "INSERT INTO Cartera (DNI, id_accion, cantidad, precioTotal) VALUES (@DNI ,@id ,@cantidad ,@importe)"
                    cnn = New SqlConnection(CADENA_CONEXION_UAXINVER)
                    cnn.Open()
                    Dim com7 As SqlCommand = New SqlCommand(insert, cnn)

                    com7.Parameters.Add("@DNI", SqlDbType.NVarChar, 50)
                    com7.Parameters(0).Value = DNI

                    com7.Parameters.Add("@id", SqlDbType.NVarChar, 50)
                    com7.Parameters(1).Value = id

                    com7.Parameters.Add("@cantidad", SqlDbType.Int)
                    com7.Parameters(2).Value = cantidad

                    com7.Parameters.Add("@importe", SqlDbType.Money, 50)
                    com7.Parameters(3).Value = importe

                    Dim res7 As Integer = com7.ExecuteNonQuery()
                    If cnn.State = ConnectionState.Open Then
                        cnn.Close()
                    End If
                    Return res7
                End If
            Else
                Return -2
            End If
            Return -1
        End Function 'comprarAccion

        Public Shared Function comprarAccionPorValor(ByVal DNI As String, ByVal id As String, ByVal cantidad As Integer, ByVal mejor As Double) As Integer

            Dim query As String = "SELECT saldo FROM Usuarios WHERE DNI = @DNI"
            cnn = New SqlConnection(CADENA_CONEXION_UAXINVER)
            cnn.Open()
            Dim com As New SqlCommand(query, cnn)

            com.Parameters.Add("@DNI", SqlDbType.NVarChar, 50)
            com.Parameters(0).Value = DNI

            Dim sqldr As SqlDataReader = com.ExecuteReader()
            Dim lista As List(Of String()) = New List(Of String())
            Dim datos(sqldr.VisibleFieldCount) As String
            While sqldr.Read()
                Dim i As Integer
                For i = 0 To sqldr.VisibleFieldCount - 1
                    datos(i) = sqldr(i)
                Next i
                lista.Add(datos)
            End While
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            Dim saldo As Double = Double.Parse((lista.First.GetValue(0))) '

            
            query = "SELECT precio FROM Acciones WHERE id_accion = @id" ' ID de la compra
            cnn = New SqlConnection(CADENA_CONEXION_UAXINVER)
            cnn.Open()
            Dim com2 As New SqlCommand(query, cnn)

            com2.Parameters.Add("@id", SqlDbType.NVarChar, 50)
            com2.Parameters(0).Value = id

            Dim sqldr2 As SqlDataReader = com2.ExecuteReader()
            Dim lista2 As List(Of String()) = New List(Of String())
            Dim datos2(sqldr2.VisibleFieldCount) As String
            While sqldr2.Read()
                Dim i As Integer
                For i = 0 To sqldr2.VisibleFieldCount - 1
                    datos2(i) = sqldr2(i)
                Next i
                lista2.Add(datos2)
            End While
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            If lista2.Count = 0 Then
                Return -2
            End If
            Dim valoracion As Double = Double.Parse((lista2.First.GetValue(0))) '
            

            Dim importe As Double = cantidad * valoracion
            If importe > saldo Then
                Return -3
            Else
                If mejor = valoracion Then
                    comprarAccion(DNI, id, cantidad)

                    Return 2
                End If
                Dim fecha As DateTime = DateTime.Now
                Dim fechaStr As String = "" + fecha
                Dim insert As String = "INSERT INTO Compras(id_usuario, id_accion, cantidad, fecha, importe, precio, esPendiente, activo) VALUES (@DNI ,@id ,@cantidad , @fecha, 0 , @mejor , 1 , 0)"

                cnn = New SqlConnection(CADENA_CONEXION_UAXINVER)
                cnn.Open()
                Dim com3 As SqlCommand = New SqlCommand(insert, cnn)

                com3.Parameters.Add("@DNI", SqlDbType.NVarChar, 50)
                com3.Parameters(0).Value = DNI

                com3.Parameters.Add("@id", SqlDbType.NVarChar, 50)
                com3.Parameters(1).Value = id

                com3.Parameters.Add("@cantidad", SqlDbType.Int)
                com3.Parameters(2).Value = cantidad

                com3.Parameters.Add("@mejor", SqlDbType.Money, 50)
                com3.Parameters(3).Value = mejor

                com3.Parameters.Add("@fecha", SqlDbType.DateTime)
                com3.Parameters(4).Value = DateTime.Now

                Dim res3 As Integer = com3.ExecuteNonQuery()
                If cnn.State = ConnectionState.Open Then
                    cnn.Close()
                End If

                If res3 > 0 Then
                    Return 1
                Else
                    Return -4
                End If
            End If
            Return -1
        End Function 'comprarAccionPorValor


        Public Shared Function venderAccion(ByVal DNI As String, ByVal id As String, ByVal cantidad As Integer) As Integer
            'Se obtiene el saldo 
            Dim query As String = "SELECT saldo FROM Usuarios WHERE DNI = @DNI"
            cnn = New SqlConnection(CADENA_CONEXION_UAXINVER)
            cnn.Open()
            Dim com As New SqlCommand(query, cnn)
            com.Parameters.Add("@DNI", SqlDbType.NVarChar, 50)
            com.Parameters(0).Value = DNI
            Dim sqldr As SqlDataReader = com.ExecuteReader()
            Dim lista As List(Of String()) = New List(Of String())
            Dim datos(sqldr.VisibleFieldCount) As String
            While sqldr.Read()
                Dim i As Integer
                For i = 0 To sqldr.VisibleFieldCount - 1
                    datos(i) = sqldr(i)
                Next i
                lista.Add(datos)
            End While
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            Dim saldo As Double = Double.Parse((lista.First.GetValue(0))) '
           
            query = "SELECT precio FROM Acciones WHERE id_accion = @id" 'Se obtiene el ID de la compra
            cnn = New SqlConnection(CADENA_CONEXION_UAXINVER)
            cnn.Open()
            Dim com2 As New SqlCommand(query, cnn)
            com2.Parameters.Add("@id", SqlDbType.NVarChar, 50)
            com2.Parameters(0).Value = id
            Dim sqldr2 As SqlDataReader = com2.ExecuteReader()
            Dim lista2 As List(Of String()) = New List(Of String())
            Dim datos2(sqldr2.VisibleFieldCount) As String
            While sqldr2.Read()
                Dim i As Integer
                For i = 0 To sqldr2.VisibleFieldCount - 1
                    datos2(i) = sqldr2(i)
                Next i
                lista2.Add(datos2)
            End While
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            Dim valoracion As Double = Double.Parse((lista2.First.GetValue(0))) '
            
            Dim importe As Double = cantidad * valoracion
            Dim saldoActualizado As Double = saldo + importe

            query = "UPDATE Usuarios SET saldo =  @saldoActualizado WHERE DNI= @DNI" 'Se obtiene el saldo actualizadooo

            cnn = New SqlConnection(CADENA_CONEXION_UAXINVER)
            cnn.Open()
            Dim com3 As New SqlCommand(query, cnn)
            com3.Parameters.Add("@DNI", SqlDbType.NVarChar, 50)
            com3.Parameters(0).Value = DNI
            com3.Parameters.Add("@saldoActualizado", SqlDbType.Money)
            com3.Parameters(1).Value = saldoActualizado
            Dim sqldr3 As SqlDataReader = com3.ExecuteReader()
            Dim lista3 As List(Of String()) = New List(Of String())
            Dim datos3(sqldr3.VisibleFieldCount) As String
            While sqldr3.Read()
                Dim i As Integer
                For i = 0 To sqldr3.VisibleFieldCount - 1
                    datos3(i) = sqldr3(i)
                Next i
                lista3.Add(datos3)
            End While
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If

            query = "SELECT MAX(id_venta) FROM Ventas"
            cnn = New SqlConnection(CADENA_CONEXION_UAXINVER)
            cnn.Open()
            Dim com8 As New SqlCommand(query, cnn)
            Dim sqldr8 As SqlDataReader = com8.ExecuteReader()
            Dim lista8 As List(Of String()) = New List(Of String())
            Dim datos8(sqldr8.VisibleFieldCount) As String
            While sqldr8.Read()
                Dim i As Integer
                For i = 0 To sqldr8.VisibleFieldCount - 1
                    datos8(i) = sqldr8(i)
                Next i
                lista8.Add(datos8)
            End While
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            Dim contador As Integer = Integer.Parse((lista8.First.GetValue(0))) '

            Dim fecha As DateTime = DateTime.Now
            Dim fechaStr As String = "" + fecha
            Dim insert As String = "INSERT INTO Ventas(id_venta, id_usuario, id_accion, cantidad, fecha, importe, precio, esPendiente) VALUES (@id_venta, @DNI, @id , @cantidad, @fecha , @importe, @valoracion , 0)"

            cnn = New SqlConnection(CADENA_CONEXION_UAXINVER)
            cnn.Open()
            Dim com5 As SqlCommand = New SqlCommand(insert, cnn)

            com5.Parameters.Add("@DNI", SqlDbType.NVarChar, 50)
            com5.Parameters(0).Value = DNI

            com5.Parameters.Add("@id", SqlDbType.NVarChar, 50)
            com5.Parameters(1).Value = id

            com5.Parameters.Add("@cantidad", SqlDbType.Int)
            com5.Parameters(2).Value = cantidad

            com5.Parameters.Add("@importe", SqlDbType.Money, 50)
            com5.Parameters(3).Value = importe

            com5.Parameters.Add("@valoracion", SqlDbType.Money, 50)
            com5.Parameters(4).Value = valoracion

            com5.Parameters.Add("@fecha", SqlDbType.DateTime)
            com5.Parameters(5).Value = Date.Now

            com5.Parameters.Add("@id_venta", SqlDbType.Int)
            com5.Parameters(6).Value = contador + 1

            Dim res5 As Integer = com5.ExecuteNonQuery()
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If

            query = "UPDATE Cartera SET cantidad = cantidad - @cantidad , precioTotal = precioTotal + @importe WHERE DNI = @DNI AND id_accion = @id"
            cnn = New SqlConnection(CADENA_CONEXION_UAXINVER)
            cnn.Open()
            Dim com4 As SqlCommand = New SqlCommand(query, cnn)

            com4.Parameters.Add("@DNI", SqlDbType.NVarChar, 50)
            com4.Parameters(0).Value = DNI

            com4.Parameters.Add("@id", SqlDbType.NVarChar, 50)
            com4.Parameters(1).Value = id

            com4.Parameters.Add("@cantidad", SqlDbType.Int)
            com4.Parameters(2).Value = cantidad

            com4.Parameters.Add("@importe", SqlDbType.Money, 50)
            com4.Parameters(3).Value = importe

            Dim res4 As Integer = com4.ExecuteNonQuery()
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            Console.WriteLine("Venta realizada con éxito")
            Return res4
        End Function 'venderAccion

        Public Shared Function venderAccionPorValor(ByVal DNI As String, ByVal id As String, ByVal cantidad As Integer, ByVal mejor As Double) As Integer
            'Se obtiene el saldo 
            Dim query As String = "SELECT saldo FROM Usuarios WHERE DNI = @DNI"
            cnn = New SqlConnection(CADENA_CONEXION_UAXINVER)
            cnn.Open()
            Dim com As New SqlCommand(query, cnn)

            com.Parameters.Add("@DNI", SqlDbType.NVarChar, 50)
            com.Parameters(0).Value = DNI

            Dim sqldr As SqlDataReader = com.ExecuteReader()
            Dim lista As List(Of String()) = New List(Of String())
            Dim datos(sqldr.VisibleFieldCount) As String
            While sqldr.Read()
                Dim i As Integer
                For i = 0 To sqldr.VisibleFieldCount - 1
                    datos(i) = sqldr(i)
                Next i
                lista.Add(datos)
            End While
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            Dim saldo As Double = Double.Parse((lista.First.GetValue(0))) '
            
            query = "SELECT precio FROM Acciones WHERE id_accion = @id" 'ID de la compra
            cnn = New SqlConnection(CADENA_CONEXION_UAXINVER)
            cnn.Open()
            Dim com2 As New SqlCommand(query, cnn)

            com2.Parameters.Add("@id", SqlDbType.NVarChar, 50)
            com2.Parameters(0).Value = id

            Dim sqldr2 As SqlDataReader = com2.ExecuteReader()
            Dim lista2 As List(Of String()) = New List(Of String())
            Dim datos2(sqldr2.VisibleFieldCount) As String
            While sqldr2.Read()
                Dim i As Integer
                For i = 0 To sqldr2.VisibleFieldCount - 1
                    datos2(i) = sqldr2(i)
                Next i
                lista2.Add(datos2)
            End While
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            Dim valoracion As Double = Double.Parse((lista2.First.GetValue(0))) '
            
            If valoracion = mejor Then
                venderAccion(DNI, id, cantidad)
                Return 2
            Else

                query = "SELECT MAX(id_venta) FROM Ventas"
                cnn = New SqlConnection(CADENA_CONEXION_UAXINVER)
                cnn.Open()
                Dim com8 As New SqlCommand(query, cnn)
                Dim sqldr8 As SqlDataReader = com8.ExecuteReader()
                Dim lista8 As List(Of String()) = New List(Of String())
                Dim datos8(sqldr8.VisibleFieldCount) As String
                While sqldr8.Read()
                    Dim i As Integer
                    For i = 0 To sqldr8.VisibleFieldCount - 1
                        datos8(i) = sqldr8(i)
                    Next i
                    lista8.Add(datos8)
                End While
                If cnn.State = ConnectionState.Open Then
                    cnn.Close()
                End If
                Dim contador As Integer = Integer.Parse((lista8.First.GetValue(0))) '



                Dim fecha As DateTime = DateTime.Now
                Dim fechaStr As String = "" + fecha
                Dim insert As String = "INSERT INTO Ventas(id_venta, id_usuario, id_accion, cantidad, fecha, importe, precio, esPendiente) VALUES (@id_venta, @DNI , @id , @cantidad , GetDate(),0, @mejor, 1)"
                cnn = New SqlConnection(CADENA_CONEXION_UAXINVER)
                cnn.Open()
                Dim com3 As SqlCommand = New SqlCommand(insert, cnn)

                com3.Parameters.Add("@DNI", SqlDbType.NVarChar, 50)
                com3.Parameters(0).Value = DNI

                com3.Parameters.Add("@id", SqlDbType.NVarChar, 50)
                com3.Parameters(1).Value = id

                com3.Parameters.Add("@cantidad", SqlDbType.Int)
                com3.Parameters(2).Value = cantidad

                com3.Parameters.Add("@mejor", SqlDbType.Money, 50)
                com3.Parameters(3).Value = mejor

                com3.Parameters.Add("@id_venta", SqlDbType.Int)
                com3.Parameters(4).Value = contador + 1

                Dim res3 As Integer = com3.ExecuteNonQuery()
                If cnn.State = ConnectionState.Open Then
                    cnn.Close()
                End If
                If res3 > 0 Then
                    Return 1
                Else
                    Return -1
                End If
            End If
        End Function 'venderAccionPorValor



        Public Shared Function borrarCompras(ByVal idC As Integer) As Boolean
            Dim query As String = "DELETE * from Compras WHERE idC = @idC"
            cnn = New SqlConnection(CADENA_CONEXION_UAXINVER)
            cnn.Open()
            Dim com As SqlCommand = New SqlCommand(query, cnn)

            com.Parameters.Add("@idC", SqlDbType.Int)
            com.Parameters(0).Value = idC

            Dim res As Integer = com.ExecuteNonQuery()
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            Return res > 0
        End Function 'borrarCompras

        Public Shared Function borrarVenta(ByVal idV As Integer) As Boolean
            Dim query As String = "DELETE * from Ventas WHERE id_venta = @idV"
            cnn = New SqlConnection(CADENA_CONEXION_UAXINVER)
            cnn.Open()
            Dim com As SqlCommand = New SqlCommand(query, cnn)

            com.Parameters.Add("@idV", SqlDbType.Int)
            com.Parameters(0).Value = idV

            Dim res As Integer = com.ExecuteNonQuery()
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            Return res > 0
        End Function 'borrarVentas


        Public Shared Function cancelarOrdenCompras(ByVal idc As String) As Boolean
            Dim query As String = ""
            query = "DELETE FROM Compras WHERE id_compra = @idc AND esPendiente = 1"
            cnn = New SqlConnection(CADENA_CONEXION_UAXINVER)
            cnn.Open()
            Dim com As SqlCommand = New SqlCommand(query, cnn)

            com.Parameters.Add("@idc", SqlDbType.Int)
            com.Parameters(0).Value = idc

            Dim res As Integer = com.ExecuteNonQuery()
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            Return res > 0
        End Function 'cancelarOrdenCompras

        Public Shared Function cancelarOrdenVenta(ByVal idv As Integer) As Boolean
            Dim query As String = ""
            query = "DELETE * FROM Ventas WHERE id_venta = @idv AND esPendiente = 1"
            cnn = New SqlConnection(CADENA_CONEXION_UAXINVER)
            cnn.Open()
            Dim com As SqlCommand = New SqlCommand(query, cnn)

            com.Parameters.Add("@idv", SqlDbType.NVarChar, 50)
            com.Parameters(0).Value = idv

            Dim res As Integer = com.ExecuteNonQuery()
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            Return res > 0
        End Function 'cancelarOrdenVentas


        Public Shared Function getInforme() As String

            Dim compraVentas As String = ""
            Dim query As String = "SELECT SUM(importe) FROM Compras WHERE esPendiente = 0 AND activo= 1"
            cnn = New SqlConnection(CADENA_CONEXION_UAXINVER)
            cnn.Open()
            Dim com As New SqlCommand(query, cnn)
            Dim sqldr As SqlDataReader = com.ExecuteReader()
            Dim lista As List(Of String()) = New List(Of String())
            Dim datos(sqldr.VisibleFieldCount) As String
            While sqldr.Read()
                Dim i As Integer
                For i = 0 To sqldr.VisibleFieldCount - 1
                    datos(i) = sqldr(i)
                Next i
                lista.Add(datos)
            End While
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If

            Dim importeTotalCompras As Double = Double.Parse((lista.First.GetValue(0))) '


            query = "SELECT SUM(importe) FROM Ventas WHERE esPendiente = 0"
            cnn = New SqlConnection(CADENA_CONEXION_UAXINVER)
            cnn.Open()
            Dim com2 As New SqlCommand(query, cnn)
            Dim sqldr2 As SqlDataReader = com2.ExecuteReader()
            Dim lista2 As List(Of String()) = New List(Of String())
            Dim datos2(sqldr2.VisibleFieldCount) As String
            While sqldr2.Read()
                Dim i As Integer
                For i = 0 To sqldr2.VisibleFieldCount - 1
                    datos2(i) = sqldr2(i)
                Next i
                lista2.Add(datos2)
            End While
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            Dim importeTotalVentas As Double = Double.Parse((lista2.First.GetValue(0))) '

            compraVentas = vbCrLf + "Importe total en Compras :" + importeTotalCompras.ToString + "€." + vbCrLf + vbCrLf + "Importe total en Ventas: " + importeTotalVentas.ToString + "€."

            query = "SELECT COUNT(id_compra) FROM Compras WHERE esPendiente = 0 AND activo = 1"
            cnn = New SqlConnection(CADENA_CONEXION_UAXINVER)
            cnn.Open()
            Dim com3 As New SqlCommand(query, cnn)
            Dim sqldr3 As SqlDataReader = com3.ExecuteReader()
            Dim lista3 As List(Of String()) = New List(Of String())
            Dim datos3(sqldr3.VisibleFieldCount) As String
            While sqldr3.Read()
                Dim i As Integer
                For i = 0 To sqldr3.VisibleFieldCount - 1
                    datos3(i) = sqldr3(i)
                Next i
                lista3.Add(datos3)
            End While
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            Dim numCompras As Integer = Integer.Parse((lista3.First.GetValue(0))) '


            query = "SELECT COUNT(id_venta) FROM Ventas WHERE esPendiente = 0"
            cnn = New SqlConnection(CADENA_CONEXION_UAXINVER)
            cnn.Open()
            Dim com4 As New SqlCommand(query, cnn)
            Dim sqldr4 As SqlDataReader = com4.ExecuteReader()
            Dim lista4 As List(Of String()) = New List(Of String())
            Dim datos4(sqldr4.VisibleFieldCount) As String
            While sqldr4.Read()
                Dim i As Integer
                For i = 0 To sqldr4.VisibleFieldCount - 1
                    datos4(i) = sqldr4(i)
                Next i
                lista4.Add(datos4)
            End While
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            Dim numVentass As Integer = Integer.Parse((lista4.First.GetValue(0))) '


            compraVentas += vbCrLf + vbCrLf + "Numero de operaciones de compra totales: " + numCompras.ToString + vbCrLf + vbCrLf + "Numero de operaciones de venta totales: " + numVentass.ToString

            Return compraVentas
        End Function 'getInforme


        Public Shared Function listarUsuarios(ByVal esPendiente As Integer) As String
            Dim impresion As String = ""

            Dim query As String = "SELECT * FROM Usuarios WHERE esPendiente = @esPendiente"

            cnn = New SqlConnection(CADENA_CONEXION_UAXINVER)
            cnn.Open()
            Dim com As New SqlCommand(query, cnn)
            com.Parameters.Add("@esPendiente", SqlDbType.Int)
            com.Parameters(0).Value = esPendiente
            Dim listaUsuarios As List(Of Usuario) = New List(Of Usuario)
            Dim sqldr As SqlDataReader = com.ExecuteReader()
            Dim lista As List(Of String()) = New List(Of String())
            Dim datos(sqldr.VisibleFieldCount) As String
            Dim i As Integer
            While sqldr.Read()
                For i = 0 To sqldr.VisibleFieldCount - 1
                    datos(i) = sqldr(i)
                Next i
                lista.Add(datos)
                listaUsuarios.Add(New Usuario(datos))
            End While
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If

            For i = 0 To listaUsuarios.Count - 1
                impresion += "[" + i.ToString + "]" + vbCrLf + "DNI: " + listaUsuarios(i).getDNI() + vbCrLf + "nombre: " + listaUsuarios(i).getnombre() + vbCrLf + "apellidos: " + listaUsuarios(i).getapellidos() + vbCrLf
            Next i

            Return impresion
        End Function 'listarUsuarios

        Public Shared Function listarAcciones() As String
            Dim impresion As String = ""
            Dim accion As String() = New String() {" "}
            Dim listaAcciones As List(Of Accion) = New List(Of Accion)
            Dim query As String = "SELECT * FROM Acciones"
            cnn = New SqlConnection(CADENA_CONEXION_UAXINVER)
            cnn.Open()
            Dim com As New SqlCommand(query, cnn)
            Dim sqldr As SqlDataReader = com.ExecuteReader()
            Dim lista As List(Of String()) = New List(Of String())
            Dim datos(sqldr.VisibleFieldCount) As String
            Dim i As Integer
            While sqldr.Read()
                For i = 0 To sqldr.VisibleFieldCount - 1
                    datos(i) = sqldr(i)
                Next i
                lista.Add(datos)
                listaAcciones.Add(New Accion(datos))
            End While
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If

            For i = 0 To listaAcciones.Count - 1
                impresion += "[" + i.ToString + "]" + vbCrLf + "ID: " + listaAcciones(i).getId().ToString + vbCrLf + "Valor: " + listaAcciones(i).getValor().ToString + " €/accion" + vbCrLf + "nombre: " + listaAcciones(i).getnombre() + vbCrLf
            Next i

            Return impresion
        End Function 'listarAcciones


        Public Shared Function listarCompras(ByVal esPendiente As Integer, ByVal DNI As String, ByVal activo As Integer) As String
            Dim impresion As String = ""
            Dim query As String = "SELECT * FROM Compras WHERE esPendiente = @esPendiente AND id_usuario = @DNI AND activo = @activo"
            cnn = New SqlConnection(CADENA_CONEXION_UAXINVER)
            cnn.Open()
            Dim com As New SqlCommand(query, cnn)

            com.Parameters.Add("@esPendiente", SqlDbType.Int)
            com.Parameters(0).Value = esPendiente

            com.Parameters.Add("@DNI", SqlDbType.NVarChar, 50)
            com.Parameters(1).Value = DNI

            com.Parameters.Add("@activo", SqlDbType.Int)
            com.Parameters(2).Value = activo

            Dim sqldr As SqlDataReader = com.ExecuteReader()
            Dim lista As List(Of String()) = New List(Of String())
            Dim datos(sqldr.VisibleFieldCount) As String
            Dim listaCompras As List(Of Compras) = New List(Of Compras)
            Dim i As Integer
            While sqldr.Read()

                For i = 0 To sqldr.VisibleFieldCount - 1
                    datos(i) = sqldr(i)
                Next i
                lista.Add(datos)
                listaCompras.Add(New Compras(datos))
            End While
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If

            For i = 0 To listaCompras.Count - 1
                impresion += vbCrLf + "[" + i.ToString + "]" + vbCrLf + "ID: " + listaCompras(i).getIdC().ToString + vbCrLf + "Simbolo Accion: " + listaCompras(i).getid_accion() + vbCrLf + "Número de Acciones: " + listaCompras(i).getNumAcciones().ToString + vbCrLf + "Fecha de la operacion: " + listaCompras(i).getFechaOperacion().ToString() + vbCrLf + "Precio de Acciones: " + listaCompras(i).getPrecioAccion().ToString + " €/accion" + vbCrLf + "Importe de la operacion: " + listaCompras(i).getImporteOperacion().ToString + " €" + vbCrLf + vbCrLf
            Next i

            Return impresion
        End Function 'listarCompras

        Public Shared Function listarVenta(ByVal esPendiente As Integer, ByVal DNI As String) As String
            Dim impresion As String = ""
            Dim query As String = "SELECT * FROM Ventas WHERE esPendiente = @esPendiente AND id_usuario = @dni"
            cnn = New SqlConnection(CADENA_CONEXION_UAXINVER)
            cnn.Open()
            Dim com As New SqlCommand(query, cnn)

            com.Parameters.Add("@esPendiente", SqlDbType.Int)
            com.Parameters(0).Value = esPendiente

            com.Parameters.Add("@DNI", SqlDbType.NVarChar, 50)
            com.Parameters(1).Value = DNI

            Dim sqldr As SqlDataReader = com.ExecuteReader()
            Dim lista As List(Of String()) = New List(Of String())
            Dim datos(sqldr.VisibleFieldCount) As String
            Dim listaVentass As List(Of Compras) = New List(Of Compras)
            Dim i As Integer
            While sqldr.Read()
                For i = 0 To sqldr.VisibleFieldCount - 1

                    datos(i) = sqldr(i).ToString
                Next i
                listaVentass.Add(New Compras(datos))
                lista.Add(datos)
            End While
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If

            For i = 0 To listaVentass.Count - 1
                impresion += vbCrLf + "[" + i.ToString + "]" + vbCrLf + "ID: " + listaVentass(i).getIdC().ToString + vbCrLf + "Simbolo Accion: " + listaVentass(i).getid_accion().ToString + vbCrLf + "Número de Acciones: " + listaVentass(i).getNumAcciones().ToString + vbCrLf + "Fecha de la operacion: " + listaVentass(i).getFechaOperacion().ToString() + vbCrLf + "Precio de Acciones: " + listaVentass(i).getPrecioAccion().ToString + " €/accion" + vbCrLf + "Importe de la operacion: " + listaVentass(i).getImporteOperacion().ToString + " €" + vbCrLf + vbCrLf
            Next i
            Return impresion
        End Function 'listarVentas

        Public Shared Function listarCartera(ByVal DNI As String) As String
            Dim impresion As String = ""
            Dim query As String = "SELECT * FROM Cartera WHERE DNI = @dni"
            cnn = New SqlConnection(CADENA_CONEXION_UAXINVER)
            cnn.Open()
            Dim com As New SqlCommand(query, cnn)

            com.Parameters.Add("@DNI", SqlDbType.NVarChar, 50)
            com.Parameters(0).Value = DNI
            Dim listaCartera As List(Of elementoCartera) = New List(Of elementoCartera)
            Dim sqldr As SqlDataReader = com.ExecuteReader()
            Dim lista As List(Of String()) = New List(Of String())
            Dim datos(sqldr.VisibleFieldCount) As String
            Dim i As Integer
            While sqldr.Read()

                For i = 0 To sqldr.VisibleFieldCount - 1
                    datos(i) = sqldr(i)
                Next i
                lista.Add(datos)
                listaCartera.Add(New elementoCartera(datos))
            End While
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            Dim y As Integer = 0

            For y = 0 To listaCartera.Count - 1
                query = "SELECT precio FROM Acciones WHERE id_accion = @id"
                cnn = New SqlConnection(CADENA_CONEXION_UAXINVER)
                cnn.Open()
                Dim com2 As New SqlCommand(query, cnn)

                com2.Parameters.Add("@id", SqlDbType.NVarChar, 50)
                com2.Parameters(0).Value = listaCartera(y).getIdAccion()
                Dim sqldr2 As SqlDataReader = com2.ExecuteReader()
                Dim lista2 As List(Of String()) = New List(Of String())
                Dim datos2(sqldr2.VisibleFieldCount) As String
                Dim x As Integer
                While sqldr2.Read()

                    For x = 0 To sqldr2.VisibleFieldCount - 1
                        datos2(x) = sqldr2(x)
                    Next x
                    lista2.Add(datos2)
                End While

                If cnn.State = ConnectionState.Open Then
                    cnn.Close()
                End If
                Dim valor As Double = Double.Parse(lista2.First.GetValue(0))
                impresion += "[" + y.ToString + "]" + vbCrLf + "ID Acción: " + listaCartera(y).getIdAccion().ToString + vbCrLf + "Precio por acción actual: " + valor.ToString + " €/accion" + vbCrLf + "Numero de acciones: " + listaCartera(y).getNumTotal().ToString + vbCrLf + "Valor Total:" + listaCartera(y).getValorTotal().ToString + " €" + vbCrLf + vbCrLf
            Next y
            Return impresion
        End Function 'listarCartera


        Public Shared Function existeAccion(ByVal simboloAccion As String) As Boolean
            Dim query As String = "SELECT id_accion FROM Acciones WHERE id_accion = @simboloAccion"
            cnn = New SqlConnection(CADENA_CONEXION_UAXINVER)
            cnn.Open()
            Dim com As New SqlCommand(query, cnn)

            com.Parameters.Add("@simboloAccion", SqlDbType.NVarChar, 50)
            com.Parameters(0).Value = simboloAccion

            Dim sqldr As SqlDataReader = com.ExecuteReader()
            Dim lista As List(Of String()) = New List(Of String())
            Dim datos(sqldr.VisibleFieldCount) As String
            While sqldr.Read()
                Dim i As Integer
                For i = 0 To sqldr.VisibleFieldCount - 1
                    datos(i) = sqldr(i)
                Next i
                lista.Add(datos)
            End While
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If

            Return lista.Count > 0
        End Function 'existeAccion


        Public Shared Function actualizarComprasPendientes() As Boolean
            Dim primerResultado As Integer

            Dim query As String = "UPDATE Compras SET Compras.precio = Acciones.precio, Compras.importe = (Compras.cantidad * Acciones.precio), Compras.fecha = GetDate() FROM Compras INNER JOIN Acciones ON Compras.id_accion = Acciones.id_accion AND Compras.precio >= Acciones.precio WHERE Compras.esPendiente = 1"
            cnn = New SqlConnection(CADENA_CONEXION_UAXINVER)
            cnn.Open()
            Dim com As SqlCommand = New SqlCommand(query, cnn)

            primerResultado = com.ExecuteNonQuery()
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            If primerResultado > 0 Then
                query = "SELECT DISTINCT Count(Cartera.id_accion) FROM Compras INNER JOIN Cartera ON (Compras.id_accion = Cartera.id_accion) AND (Compras.id_usuario = Cartera.DNI) GROUP BY Compras.esPendiente HAVING (((Compras.esPendiente)=1))"
                cnn = New SqlConnection(CADENA_CONEXION_UAXINVER)
                cnn.Open()
                Dim com8 As SqlCommand = New SqlCommand(query, cnn)

                Dim sqldr8 As SqlDataReader = com8.ExecuteReader()
                Dim lista8 As List(Of String()) = New List(Of String())
                Dim datos8(sqldr8.VisibleFieldCount) As String
                While sqldr8.Read()
                    Dim i As Integer
                    For i = 0 To sqldr8.VisibleFieldCount - 1
                        datos8(i) = sqldr8(i)
                    Next i
                    lista8.Add(datos8)
                End While
                If cnn.State = ConnectionState.Open Then
                    cnn.Close()
                End If
                Dim numAccExistentes As Integer = lista8.Count
                If numAccExistentes > 0 Then
                    Dim res As Integer
                    query = "UPDATE Cartera SET Cartera.cantidad = Cartera.cantidad + (SELECT SUM(DISTINCT cantidad) FROM Compras WHERE Compras.id_accion = Cartera.id_accion AND esPendiente = 1) FROM Compras INNER JOIN Cartera ON Compras.id_usuario = Cartera.DNI AND Compras.id_accion = Cartera.id_accion WHERE Compras.esPendiente=1 AND Compras.activo = 0"
                    cnn = New SqlConnection(CADENA_CONEXION_UAXINVER)
                    cnn.Open()
                    Dim com3 As SqlCommand = New SqlCommand(query, cnn)

                    res = com3.ExecuteNonQuery()
                    If cnn.State = ConnectionState.Open Then
                        cnn.Close()
                    End If
                    query = "INSERT INTO Cartera ( DNI, id_accion, cantidad, precioTotal ) SELECT Compras.id_usuario, Compras.id_accion, Compras.cantidad, Compras.importe FROM Compras WHERE NOT EXISTS (SELECT id_accion FROM Cartera WHERE Cartera.id_accion = Compras.id_accion)"
                    cnn = New SqlConnection(CADENA_CONEXION_UAXINVER)
                    cnn.Open()
                    Dim com2 As SqlCommand = New SqlCommand(query, cnn)

                    res = com2.ExecuteNonQuery()
                    If cnn.State = ConnectionState.Open Then
                        cnn.Close()
                    End If
                Else
                    query = "INSERT INTO Cartera ( DNI, id_accion, cantidad, precioTotal ) SELECT Compras.id_usuario, Compras.id_accion, Compras.cantidad, Compras.importe FROM Compras WHERE NOT EXISTS (SELECT id_accion FROM Cartera WHERE Cartera.id_accion = Compras.id_accion)"
                    cnn = New SqlConnection(CADENA_CONEXION_UAXINVER)
                    cnn.Open()
                    Dim com4 As SqlCommand = New SqlCommand(query, cnn)

                    Dim res As Integer = com4.ExecuteNonQuery()
                    If cnn.State = ConnectionState.Open Then
                        cnn.Close()
                    End If
                End If
                query = "UPDATE Cartera SET Cartera.precioTotal = Cartera.cantidad * Acciones.precio FROM Cartera INNER JOIN Acciones ON Cartera.id_accion = Acciones.id_accion"
                cnn = New SqlConnection(CADENA_CONEXION_UAXINVER)
                cnn.Open()
                Dim com5 As SqlCommand = New SqlCommand(query, cnn)

                com5.ExecuteNonQuery()
                If cnn.State = ConnectionState.Open Then
                    cnn.Close()
                End If
                query = "UPDATE Usuarios SET Usuarios.saldo = Usuarios.saldo - (SELECT SUM(importe) FROM Compras WHERE esPendiente = 1 AND activo = 0) FROM Usuarios INNER JOIN Compras ON Usuarios.DNI = Compras.id_usuario WHERE Usuarios.saldo > (SELECT SUM(importe) FROM Compras WHERE Compras.id_usuario = Usuarios.DNI)"
                cnn = New SqlConnection(CADENA_CONEXION_UAXINVER)
                cnn.Open()
                Dim com6 As SqlCommand = New SqlCommand(query, cnn)

                com6.ExecuteNonQuery()
                If cnn.State = ConnectionState.Open Then
                    cnn.Close()
                End If
                query = "UPDATE Compras SET Compras.esPendiente = 0, Compras.activo = 1 WHERE Compras.esPendiente = 1 AND activo = 0"
                cnn = New SqlConnection(CADENA_CONEXION_UAXINVER)
                cnn.Open()
                Dim com7 As SqlCommand = New SqlCommand(query, cnn)

                com7.ExecuteNonQuery()
                If cnn.State = ConnectionState.Open Then
                    cnn.Close()
                End If
                Return True
            End If
            Return False
        End Function 'actualizarComprasPendientes

        Public Shared Function actualizarVentasPendientes() As Boolean
            Dim primerResultado As Integer

            Dim query As String = "UPDATE Ventas SET Ventas.precio = Acciones.precio, Ventas.importe = (Ventas.cantidad * Acciones.precio), Ventas.fecha = GetDate() FROM Ventas INNER JOIN Acciones ON Ventas.id_accion = Acciones.id_accion AND Acciones.precio >= Ventas.precio  WHERE Ventas.esPendiente = 1"
            cnn = New SqlConnection(CADENA_CONEXION_UAXINVER)
            cnn.Open()
            Dim com5 As SqlCommand = New SqlCommand(query, cnn)

            primerResultado = com5.ExecuteNonQuery()
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            If primerResultado > 0 Then
                query = "UPDATE Cartera SET Cartera.cantidad = Cartera.cantidad - (SELECT SUM(DISTINCT cantidad) FROM Ventas WHERE esPendiente = 1) FROM Ventas INNER JOIN Cartera ON Ventas.id_usuario = Cartera.DNI AND Ventas.id_accion = Cartera.id_accion WHERE Ventas.esPendiente=1"
                cnn = New SqlConnection(CADENA_CONEXION_UAXINVER)
                cnn.Open()
                Dim com As SqlCommand = New SqlCommand(query, cnn)
                com.ExecuteNonQuery()
                If cnn.State = ConnectionState.Open Then
                    cnn.Close()
                End If
                query = "UPDATE Cartera SET Cartera.precioTotal = Cartera.cantidad * Acciones.precio FROM Cartera INNER JOIN Acciones ON Cartera.id_accion = Acciones.id_accion"
                cnn = New SqlConnection(CADENA_CONEXION_UAXINVER)
                cnn.Open()
                Dim com2 As SqlCommand = New SqlCommand(query, cnn)
                com2.ExecuteNonQuery()
                If cnn.State = ConnectionState.Open Then
                    cnn.Close()
                End If
                query = "UPDATE Usuarios SET Usuarios.saldo = Usuarios.saldo + (SELECT SUM(importe) FROM Ventas WHERE esPendiente = 1) FROM Usuarios INNER JOIN Ventas ON Usuarios.DNI = Ventas.id_usuario"
                cnn = New SqlConnection(CADENA_CONEXION_UAXINVER)
                cnn.Open()
                Dim com3 As SqlCommand = New SqlCommand(query, cnn)
                com3.ExecuteNonQuery()
                If cnn.State = ConnectionState.Open Then
                    cnn.Close()
                End If
                query = "UPDATE Ventas SET Ventas.esPendiente = 0 WHERE Ventas.esPendiente = 1"
                cnn = New SqlConnection(CADENA_CONEXION_UAXINVER)
                cnn.Open()
                Dim com4 As SqlCommand = New SqlCommand(query, cnn)
                com4.ExecuteNonQuery()
                If cnn.State = ConnectionState.Open Then
                    cnn.Close()
                End If
            End If

            Return False
        End Function 'actualizarVentassPendientes
    End Class 'Consultas 
End Namespace 'BBDD
